import pandas as pd
# 显示所有列
pd.set_option('display.max_columns', None)
# 男成绩
df = pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name='男')
# 女成绩
df2 = pd.read_excel('./18级高一体测成绩汇总.xls', sheet_name='女')
# 不标准
df_score_standard = pd.read_excel('./体侧成绩评分表.xls', header=[0, 1])

df['男1000米跑'] = df['男1000米跑'].map(lambda x: float(str(x).replace('\'', '.')))
print("转换完成后 男子成绩")
print(df.head())
df_score_standard.loc[:, [('男1000米跑', '成绩'), ('女800米跑', '成绩')]] = df_score_standard.loc[:,
                                                                  [('男1000米跑', '成绩'), ('女800米跑', '成绩')]].applymap(
    lambda x: float(str(x).replace('"', '').replace('\'', '.')))
print('转换完成后 标准')
print(df_score_standard.head())

df.iloc[:, 2:] = df.iloc[:, 2:].applymap(float)
df2.iloc[:, 2:] = df2.iloc[:, 2:].applymap(float)
df_score_standard = df_score_standard.applymap(float)


def conv2score(x, type):
    """
    :param x:  体测中成绩
    :param type:  体侧的项目
    :return:  分数
    """
    score = 0
    if type in ['男1000米跑', '男50米跑', '女800米跑', '女50米跑']:
        # 越小越好
        for i in range(len(df_score_standard)):
            if x <= df_score_standard[(type, '成绩')][i]:
                score = df_score_standard[(type, '分数')][i]
                break
    else:
        # 越大越好
        for i in range(len(df_score_standard)):
            if x >= df_score_standard[(type, '成绩')][i]:
                score = df_score_standard[(type, '分数')][i]
                break
    return score

df_score = df.iloc[:,:8].__deepcopy__()
df_score.columns = ['班级','姓名','男1000米跑分数','男50米跑分数','男跳远分数','男体前屈分数','男引体分数','男肺活量分数']
for i in range(len(df)):
    for j in range(2, df.columns.size-3):
        df_score.iloc[i,j] = conv2score(df.iloc[i,j],df.columns[j])
df = pd.concat([df,df_score.iloc[:,2:]],axis=1)
df['BMI'] = (df['体重'] / df['身高'] / df['身高'] *10000).round(2)
df = df.iloc[:,[0,1,2,11,3,12,4,13,5,14,6,15,7,16,8,9,10]]
print("男子分数： \n",df.head())

df_score = df2.iloc[:,:8].__deepcopy__()
df_score.columns = ['班级','姓名','女800米跑分数','女50米跑分数','女跳远分数','女体前屈分数','女仰卧分数','女肺活量分数']
for i in range(len(df2)):
    for j in range(2, df2.columns.size-3):
        df_score.iloc[i,j] = conv2score(df2.iloc[i,j],df2.columns[j])
df2 = pd.concat([df2,df_score.iloc[:,2:]],axis=1)
df2['BMI'] = (df2['体重'] / df2['身高'] / df2['身高'] *10000).round(2)
df2 = df2.iloc[:,[0,1,2,11,3,12,4,13,5,14,6,15,7,16,8,9,10]]
print("女子分数： \n",df2.head())

